// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections;

namespace net.windward.utils.ado.SqlServer
{
	///<summary>
	/// A lightweight handler of functionality that the ODBC and OleDb drivers can use as well as the main implementations.
	///</summary>
	public class WrSqlServerSyntax : IWrSyntax
	{

		private static readonly Hashtable reservedWords = new Hashtable();

		static WrSqlServerSyntax()
		{
			foreach (string str in _reservedWords)
				reservedWords.Add(str, str);
		}

		/// <summary>
		/// Convert a select to normalize the "top 10" syntax
		/// </summary>
		/// <param name="select">The select with our standard usage</param>
		/// <returns>The select for this database.</returns>
		public string ConvertSelect(string select)
		{
			int pos = select.ToLower().LastIndexOf(" top ");
			if (pos == -1)
				return select;

			int posSelect = select.IndexOf("select ");
			return
				select.Substring(0, posSelect + 7).Trim() + " " + select.Substring(pos).Trim() + " " +
				select.Substring(posSelect + 7, pos - posSelect - 7).Trim();
		}

		/// <summary>
		/// Create a safe table name. Generally needed only if the name has a space in it or is a reserved name.
		/// </summary>
		/// <param name="tableName">The true table name.</param>
		/// <returns>The table name as it should be used in a select.</returns>
		public string SafeTableName(string tableName)
		{
			// schema.table name -> schema.[table name]
			int dot = tableName.IndexOf('.');
			if (dot == -1)
				return MakeNameSafe(tableName);
			return tableName.Substring(0, dot) + '.' + MakeNameSafe(tableName.Substring(dot + 1));
		}

		/// <summary>
		/// Create a safe column name. Generally needed only if the name has a space in it or is a reserved name.
		/// </summary>
		/// <param name="columnName">The true column name.</param>
		/// <returns>The table name as it should be used in a select.</returns>
		public string SafeColumnName(string columnName)
		{
			return MakeNameSafe(columnName);
		}

		/// <summary>
		/// Create a safe table name. Generally needed only if the name has a space in it or is a reserved name.
		/// </summary>
		/// <param name="tableName">The true table name.</param>
		/// <param name="columnName">The true column name.</param>
		/// <returns>The table.column name as it should be used in a select.</returns>
		public string SafeTableColumnName(string tableName, string columnName)
		{
			return SafeTableName(tableName) + '.' + SafeColumnName(columnName);
		}

		private static string MakeNameSafe(string columnName)
		{
			if (columnName[0] == '[' && columnName[columnName.Length - 1] == ']')
				return columnName;

			bool makeSafe = reservedWords.ContainsKey(columnName.ToUpper());
			if (! makeSafe)
				foreach (char ch in columnName)
					if ((!Char.IsLetterOrDigit(ch)) && (ch != '_'))
					{
						makeSafe = true;
						break;
					}
			if (makeSafe)
			{
				if (columnName[0] == '[')
					return columnName + ']';
				if (columnName[columnName.Length - 1] == ']')
					return '[' + columnName;
				return '[' + columnName + ']';
			}

			return columnName;
		}

		private static readonly string[] _reservedWords = {
		                                                  	"ADD", "EXCEPT", "PERCENT", "ALL", "EXEC", "PLAN", "ALTER",
		                                                  	"EXECUTE", "PRECISION", "AND", "EXISTS", "PRIMARY", "ANY", "EXIT",
		                                                  	"PRINT", "AS", "FETCH", "PROC", "ASC", "FILE", "PROCEDURE",
		                                                  	"AUTHORIZATION", "FILLFACTOR", "PUBLIC", "BACKUP", "FOR",
		                                                  	"RAISERROR", "BEGIN", "FOREIGN", "READ", "BETWEEN", "FREETEXT",
		                                                  	"READTEXT", "BREAK", "FREETEXTTABLE", "RECONFIGURE", "BROWSE",
		                                                  	"FROM", "REFERENCES", "BULK", "FULL", "REPLICATION", "BY",
		                                                  	"FUNCTION", "RESTORE", "CASCADE", "GOTO", "RESTRICT", "CASE",
		                                                  	"GRANT", "RETURN", "CHECK", "GROUP", "REVOKE", "CHECKPOINT",
		                                                  	"HAVING", "RIGHT", "CLOSE", "HOLDLOCK", "ROLLBACK", "CLUSTERED",
		                                                  	"IDENTITY", "ROWCOUNT", "COALESCE", "IDENTITY_INSERT", "ROWGUIDCOL"
		                                                  	, "COLLATE", "IDENTITYCOL", "RULE", "COLUMN", "IF", "SAVE",
		                                                  	"COMMIT", "IN", "SCHEMA", "COMPUTE", "INDEX", "SELECT",
		                                                  	"CONSTRAINT", "INNER", "SESSION_USER", "CONTAINS", "INSERT", "SET",
		                                                  	"CONTAINSTABLE", "INTERSECT", "SETUSER", "CONTINUE", "INTO",
		                                                  	"SHUTDOWN", "CONVERT", "IS", "SOME", "CREATE", "JOIN", "STATISTICS"
		                                                  	, "CROSS", "KEY", "SYSTEM_USER", "CURRENT", "KILL", "TABLE",
		                                                  	"CURRENT_DATE", "LEFT", "TEXTSIZE", "CURRENT_TIME", "LIKE", "THEN",
		                                                  	"CURRENT_TIMESTAMP", "LINENO", "TO", "CURRENT_USER", "LOAD", "TOP",
		                                                  	"CURSOR", "NATIONAL", "TRAN", "DATABASE", "NOCHECK", "TRANSACTION",
		                                                  	"DBCC", "NONCLUSTERED", "TRIGGER", "DEALLOCATE", "NOT", "TRUNCATE",
		                                                  	"DECLARE", "NULL", "TSEQUAL", "DEFAULT", "NULLIF", "UNION",
		                                                  	"DELETE", "OF", "UNIQUE", "DENY", "OFF", "UPDATE", "DESC",
		                                                  	"OFFSETS", "UPDATETEXT", "DISK", "ON", "USE", "DISTINCT", "OPEN",
		                                                  	"USER", "DISTRIBUTED", "OPENDATASOURCE", "VALUES", "DOUBLE",
		                                                  	"OPENQUERY", "VARYING", "DROP", "OPENROWSET", "VIEW", "DUMMY",
		                                                  	"OPENXML", "WAITFOR", "DUMP", "OPTION", "WHEN", "ELSE", "OR",
		                                                  	"WHERE", "END", "ORDER", "WHILE", "ERRLVL", "OUTER", "WITH",
		                                                  	"ESCAPE", "OVER", "WRITETEXT"
		                                                  };
	}
}
